﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.OleDb;
using System.IO;
using System.Linq;
using System.Security;
using System.Web;
using System.Web.UI.WebControls;
using System.Data;
using MandoGroup.TFSIntegration;
using MandoGroup.TFSIntegration.Entities;

namespace MandoGroup.TFSEstimator
{
    public partial class _Default : System.Web.UI.Page
    {
        #region Properties
        protected string SelectedTFSProject
        {
            get { return Session["SelectedTFSProject"].ToString(); }
            set { Session["SelectedTFSProject"] = value; }
        }

        protected string SavedFilePath
        {
            get { return Session["SavedFilePath"].ToString(); }
            set { Session["SavedFilePath"] = value; }
        }

        protected string SelectedSheetName
        {
            get { return Session["SelectedSheetName"].ToString(); }
            set { Session["SelectedSheetName"] = value; }
        }

        protected string TFSColumnName
        {
            get { return Session["TFSColumnName"].ToString(); }
            set { Session["TFSColumnName"] = value; }
        }

        protected string EffortColumnName
        {
            get { return Session["EffortColumnName"].ToString(); }
            set { Session["EffortColumnName"] = value; }
        }

        protected DataSet SelectedTableData
        {
            get { return (DataSet)Session["SelectedTableData"]; }
            set { Session["SelectedTableData"] = value; }
        }

        protected List<string> ProjectNames
        {
            get { return (List<string>)Session["ProjectNames"]; }
            set { Session["ProjectNames"] = value; }
        }

        protected List<MGWorkItem> ProjectTasks
        {
            get { return (List<MGWorkItem>)Session["ProjectTasks"]; }
            set { Session["ProjectTasks"] = value; }
        }
        #endregion

        #region Events
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                if (ProjectNames == null)
                {

                    try
                    {
                        ProjectNames = TFSHelper.Instance.GetProjectNames();
                    }
                    catch(SecurityException ex)
                    {
                        //throw ex;
                        Response.Redirect("MissingTFSPermissions.aspx");
                    }
                    
                }
                tfsProjectSelector.DataSource = ProjectNames;
                tfsProjectSelector.DataBind();
            }
        }
        #endregion

        #region Methods

        protected void uploadSpreadsheetButton_OnClick(object sender, EventArgs e)
        {
            if (IsValid)
            {
                string saveDirectory = AppDomain.CurrentDomain.BaseDirectory +
                                       ConfigurationManager.AppSettings["LocalStorageFolder"].ToString();
                string filename = Path.GetFileName(fileUpload.FileName);
                SavedFilePath = saveDirectory + filename;

                try
                {
                    if (!Directory.Exists(saveDirectory))
                    {
                        Directory.CreateDirectory(saveDirectory);
                    }
                    try
                    {
                        if (File.Exists(SavedFilePath))
                            File.Delete(SavedFilePath);

                        fileUpload.PostedFile.SaveAs(SavedFilePath);

                        try
                        {

                            using (var connExcel = new OleDbConnection(GetConnectionString(false, true)))
                            {
                                connExcel.Open();

                                OleDbCommand cmdExcel = new OleDbCommand();
                                cmdExcel.Connection = connExcel;

                                sheetNamesDropdown.Items.Clear();
                                DataTable sheetsName = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" });  //Get All Sheets Name
                                foreach (DataRow row in sheetsName.Rows)
                                {
                                    sheetNamesDropdown.Items.Add(new ListItem(row["TABLE_NAME"].ToString()));
                                }

                                setSpreadsheetSettingsPanel.Visible = true;
                                uploadButtonPlaceholder.Visible = false;
                                fileUploadedPlaceholder.Visible = true;
                                fileName.Text = filename;
                            }
                        }
                        catch (Exception ex)
                        {
                            fileOpenFailed.IsValid = false;
                        }
                    }
                    catch (Exception ex)
                    {
                        fileSaveFailed.IsValid = false;
                    }
                }
                catch (Exception ex)
                {
                    directoryCreateFailed.IsValid = false;
                }
            }
        }

        /// <summary>
        /// Get the connection string to be used by the OleDbCommand
        /// </summary>
        /// <param name="updating">Whether to update an existing spreadsheet. Send false if opening a spreadsheet</param>
        /// <param name="includeHeaderRow">Whether this spreadheet has a header row</param>
        /// <returns></returns>
        protected string GetConnectionString(bool updating, bool includeHeaderRow)
        {
            return string.Format(
                "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0;{1}{2}'",
                SavedFilePath, (includeHeaderRow ? "HDR=Yes;" : ""), (updating ? "" : "IMEX=1;TypeGuessRows=0;ImportMixedTypes=Text"));
        }

        /// <summary>
        /// Get the estimate for the associated tfs id
        /// </summary>
        /// <param name="tfsId"></param>
        /// <returns></returns>
        protected double GetEstimate(int tfsId)
        {
            if (ProjectTasks.Exists(x => x.Id == tfsId))
            {
                return ProjectTasks.Single(x => x.Id == tfsId).TotalEstimate;
            }
            else
            {
                return 0;
            }
        }

        protected void chooseSpreadsheetNameDropdown_OnClick(object sender, EventArgs e)
        {

            SelectedSheetName = sheetNamesDropdown.SelectedValue;

            using (var connExcel = new OleDbConnection(GetConnectionString(false, true)))
            {
                connExcel.Open();

                OleDbCommand dbCommand = new OleDbCommand(string.Format("SELECT * FROM [{0}]", SelectedSheetName), connExcel);

                OleDbDataAdapter ada = new OleDbDataAdapter(dbCommand);
                SelectedTableData = new DataSet();
                ada.Fill(SelectedTableData);

                chooseTFSIdColumn.Items.Clear();
                chooseSumEffortColumn.Items.Clear();

                foreach (DataColumn dataColumn in SelectedTableData.Tables[0].Columns)
                {
                    chooseTFSIdColumn.Items.Add(new ListItem(dataColumn.ColumnName, dataColumn.ColumnName));
                    chooseSumEffortColumn.Items.Add(new ListItem(dataColumn.ColumnName, dataColumn.ColumnName));
                }

                processSpreadsheetPanel.Visible = true;
            }

        }

        protected void processSpreadsheet_OnClick(object sender, EventArgs e)
        {
            TFSColumnName = chooseTFSIdColumn.SelectedValue;
            EffortColumnName = chooseSumEffortColumn.SelectedValue;

            /* possible failure scenarios:
             * non integer values in the tfs id column
             * tfs id appears multiple times
             */
            bool isDataValid = true;
            List<string> nonIntIds = new List<string>();
            List<int> allIds = new List<int>();
            List<int> duplicateIds = new List<int>();
            using (OleDbConnection connExcel = new OleDbConnection(GetConnectionString(false, true)))
            {
                connExcel.Open();

                OleDbCommand dbCommand = new OleDbCommand(string.Format("SELECT * FROM [{0}]", SelectedSheetName), connExcel);

                OleDbDataAdapter ada = new OleDbDataAdapter(dbCommand);
                DataSet tempValidationDS = new DataSet();
                ada.Fill(tempValidationDS);


                foreach (DataRow row in tempValidationDS.Tables[0].Rows)
                {
                    var rowValue = row[TFSColumnName];
                    int actualTFSId = 0;
                    if (Int32.TryParse(rowValue.ToString(), out actualTFSId))
                    {
                        if (allIds.Contains(actualTFSId))
                        {
                            duplicateIds.Add(actualTFSId);
                            isDataValid = false;
                            duplicateIdsPanel.Visible = true;
                        }
                        else
                        {
                            allIds.Add(actualTFSId);
                        }
                    }
                    else
                    {
                        nonIntIds.Add(rowValue.ToString());
                        isDataValid = false;
                        nonIntIdsPanel.Visible = true;
                    }

                }

            }

            if (isDataValid)
            {
                ProcessAndDownloadSpreadsheet();
            }
            else
            {
                failedRowsPanel.Visible = true;
                processSpreadsheetButton.Visible = false;
                duplicateIdsDatagrid.DataSource = duplicateIds;
                duplicateIdsDatagrid.DataBind();
                nonIntIdsDatagrid.DataSource = nonIntIds;
                nonIntIdsDatagrid.DataBind();

            }
        }

        /// <summary>
        /// Process the uploaded spreadsheet file and then download to the browser
        /// </summary>
        protected void ProcessAndDownloadSpreadsheet()
        {
            foreach (DataRow row in SelectedTableData.Tables[0].Rows)
            {
                try
                {
                    var tfsId = Int32.Parse(row[TFSColumnName].ToString());
                    var effortSum = GetEstimate(tfsId);

                    using (OleDbConnection connExcel = new OleDbConnection(GetConnectionString(true, true)))
                    {
                        connExcel.Open();

                        string qry = "";
                        string query = string.Format("UPDATE [{0}] SET [{1}] = @EffortSum WHERE [{2}] = @TFSId",
                                                     SelectedSheetName, EffortColumnName, TFSColumnName);

                        //string query = string.Format("UPDATE [{0}] SET [{1}] = {2} WHERE [{3}] = {4}", sheetName, effortColumnName, effortSum, tfsColumnName, tfsId);
                        OleDbCommand dbCommand = new OleDbCommand(query, connExcel);
                        dbCommand.Parameters.Add(new OleDbParameter("EffortSum", effortSum));
                        dbCommand.Parameters.Add(new OleDbParameter("TFSId", tfsId));

                        dbCommand.ExecuteNonQuery();

                    }
                }
                catch (Exception ex)
                {

                }
            }

            byte[] fileBytes = File.ReadAllBytes(SavedFilePath);

            HttpResponse theResponse = HttpContext.Current.Response;
            theResponse.Clear();
            theResponse.ClearHeaders();
            theResponse.ContentType = "application/excel";
            theResponse.AddHeader("Content-Disposition",
                                  "attachment; filename=\"" + Path.GetFileName(SavedFilePath) + "\"");
            theResponse.AddHeader("Content-Length", fileBytes.Length.ToString());
            theResponse.BinaryWrite(fileBytes);
            theResponse.Flush();

            File.Delete(SavedFilePath);
        }

        protected void chooseProjectButton_OnClick(object sender, EventArgs e)
        {
            SelectedTFSProject = tfsProjectSelector.SelectedValue;
            tfsProjectSelector.Enabled = false;
            allCurrentTasksPanel.Visible = true;

            ProjectTasks = TFSHelper.Instance.GetProjectTaskWorkItems(SelectedTFSProject);
            allCurrentTasks.DataSource = ProjectTasks;
            allCurrentTasks.DataBind();

        }

        protected void confirmProcessButton_OnClick(object sender, EventArgs e)
        {
            ProcessAndDownloadSpreadsheet();
        }

        #endregion
    }
}